import pandas as pd
import numpy as np
month = '202411'
# pre_year = '202312'
# pre_month = '202410'
pre_year_int = int(int(month[0:4]) - 1)
pre_year = str(pre_year_int) + '12'
if month[4:6] != '01':
    pre_month = str(int(int(month) - 1))
else:
    pre_month = str(pre_year_int) + '12'
month_int = int(month[4:6])
# 税率
tax_rate = 0.25
# month_int = 11
# Sheet3
# 补充一个参数
xlsx_name = 'D:/repos/sicost/' + month + '/销售利润率,净资产收益率.xlsx'
df_1 = pd.read_excel(xlsx_name)
df_1 = df_1[['SERIAL_NUM', 'COMPANY_NAME', 'CURR_MONTH_VALUE_1', 'CURR_YEAR_VALUE_1']]
df_1 = df_1.reset_index(drop=False)
df_1.rename(columns={'index': 'INDEX'}, inplace=True)
df_1_1 = df_1[df_1['COMPANY_NAME'] == '非钢铁企业合计']
df_1_1 = df_1_1.reset_index(drop=True)

success = df_1_1.empty is False
if success is False:
    print('空的不需要处理')
else:
    index_tmp = df_1_1.loc[0]['INDEX']
    df_1_2 = df_1[df_1['INDEX'] < index_tmp]
    df_1_2 = df_1_2.reset_index(drop=True)
    df_1 = df_1_2
df_1.drop(['INDEX'], axis=1, inplace=True)
df_1['SERIAL_NUM_STR'] = df_1['SERIAL_NUM'].astype(str)
def __cal_strincludenum(x):
    if '0' in x.SERIAL_NUM_STR:
        rst = 1
    elif '1' in x.SERIAL_NUM_STR:
        rst = 1
    elif '2' in x.SERIAL_NUM_STR:
        rst = 1
    elif '3' in x.SERIAL_NUM_STR:
        rst = 1
    elif '4' in x.SERIAL_NUM_STR:
        rst = 1
    elif '5' in x.SERIAL_NUM_STR:
        rst = 1
    elif '6' in x.SERIAL_NUM_STR:
        rst = 1
    elif '7' in x.SERIAL_NUM_STR:
        rst = 1
    elif '8' in x.SERIAL_NUM_STR:
        rst = 1
    elif '9' in x.SERIAL_NUM_STR:
        rst = 1
    else:
        rst = 0
    return rst


df_1['include_type'] = df_1.apply(lambda x: __cal_strincludenum(x), axis=1)
df_1 = df_1[(df_1['include_type'] == 1) | (df_1['COMPANY_NAME'] == '钢铁企业合计')]
df_1 = df_1.reset_index(drop=True)

df_1.drop(['SERIAL_NUM_STR'], axis=1, inplace=True)
df_1.drop(['include_type'], axis=1, inplace=True)
df_1.rename(columns={'CURR_MONTH_VALUE_1': '本月'}, inplace=True)
df_1.rename(columns={'CURR_YEAR_VALUE_1': '本年'}, inplace=True)
df_1 = df_1[df_1['COMPANY_NAME'] != '钢铁企业合计']
df_1 = df_1.reset_index(drop=True)
writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_销售利润率.xlsx')
df_1.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
print('finish')


